﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Reflection;
using OfficeWord = Microsoft.Office.Interop.Word;
using System.Globalization;
using OfficeExcel = Microsoft.Office.Interop.Excel;

namespace DocumentManagement.Models
{
    public class YaoHuaModel
    {
        private YuanGong yg;

        internal YaoHuaModel(YuanGong yg)
        {
            this.yg = yg;
        }

        private static string path;
        private static string TempPath;
        private static string rootPath;
        private static OfficeWord.Application app;
        private static OfficeExcel.Application excelapp;

        internal static void Init(string savePath)
        {
            path = savePath + "Document\\";
            TempPath = savePath + "DocTemplate\\Temp\\";
            rootPath = savePath;
            app = new OfficeWord.Application();

            excelapp = new OfficeExcel.Application();
        }

        internal static void Uninit()
        {
            object IsSave = false;
            object missing = System.Reflection.Missing.Value;

            app.Quit(ref IsSave, ref missing, ref missing);

            excelapp.Quit();
        }  
        
        internal static string GetFilePath(Guid id)
        {
            return path + id.ToString() + ".yaohuadoc";
        }

        private YaoHuaEntities CreateYaoHuaContext(MethodBase mb)
        {
            return new YaoHuaEntities();
        }

        internal YuanGong AuthenticateUser(int uid, string pwd)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var q = from user in ctx.YuanGong
                    where user.id == uid && user.password == pwd
                    select user;

            return q.First();
        }

        internal YuanGong GetUserByIdAndPermission(int uid, PermissionLevel permission, bool doAnd)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var q = from user in ctx.YuanGong
                    where user.id == uid
                    select user;

            var u = q.First();

            if (doAnd)
            {
                if (((int)u.permission & (int)permission) == (int)permission)
                {
                    return u;
                }
            }
            else
            {
                if (((int)u.permission & (int)permission) > 0)
                {
                    return u;
                }
            }

            throw new InvalidOperationException("没有权限");
        }

        internal void ChangePassword(int uid, string oldPwd, string newPwd)
        {
            if (string.IsNullOrEmpty(newPwd))
            {
                throw new InvalidOperationException("新密码不可以为空");
            }

            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var q = from user in ctx.YuanGong
                    where user.password == oldPwd && user.id == uid
                    select user;

            var u = q.First();
            u.password = newPwd;

            ctx.SaveChanges();
        }

        internal YuGuDan CreateYuGuDan(YuGuDan ygd, int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var u = (from user in ctx.YuanGong
                     where user.id == uid
                     select user).First();

            var y = YuGuDan.CreateYuGuDan(
                Guid.NewGuid(),
                string.IsNullOrEmpty(ygd.ygbh) ? Guid.NewGuid().ToString() : ygd.ygbh,
                ygd.wtly,
                ygd.wtr,
                ygd.wtf,
                ygd.xmzl,
                ygd.jzhmj,
                ygd.tdmj,
                ygd.yt,
                ygd.jgrq,
                ygd.jzhcg,
                ygd.ygzj,
                ygd.ygdj,
                ygd.ygjzh,
                ygd.xmqy,
                ygd.gjshd,
                ygd.hxqy,
                (int)YuGuDanState.WeiChuLi,
                false,
                DateTime.Now);

            y.CreatorReference.EntityKey = u.EntityKey;

            ctx.AddToYuGuDan(y);

            ctx.SaveChanges();

            return y;
        }

        internal YuGuDan[] GetUnprocessedYuGuDan()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from ygd in ctx.YuGuDan
                    where ygd.state == (int)YuGuDanState.WeiChuLi && !ygd.isDone
                    select ygd).ToArray();
        }

        internal void AssignYuGuDanToEmployeeToChargeMoney(Guid id, int assigneeId)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var ygd = (from y in ctx.YuGuDan
                       where y.id == id && !y.isDone && y.state == (int)YuGuDanState.WeiChuLi
                       select y).First();

            var u = (from user in ctx.YuanGong
                     where user.id == assigneeId
                     select user).First();

            ygd.AssigneeReference.EntityKey = u.EntityKey;

            ygd.state = (int)YuGuDanState.AssignedToEmployeeToCharge;
            ygd.kpje = 100;

            ctx.SaveChanges();
        }

        internal void AssignYuGuDanToEmployeeToGetReport(Guid id, int assigneeId)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var ygd = (from y in ctx.YuGuDan
                       where y.id == id && !y.isDone && y.state == (int)YuGuDanState.WeiChuLi
                       select y).First();

            var u = (from user in ctx.YuanGong
                     where user.id == assigneeId
                     select user).First();

            ygd.AssigneeReference.EntityKey = u.EntityKey;

            ygd.state = (int)YuGuDanState.AssignedToEmployeeToGetReport;

            ctx.SaveChanges();
        }

        internal void DocumentYuGuDanDirectly(Guid ygId)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var ygd = (from y in ctx.YuGuDan
                       where y.id == ygId && !y.isDone && y.state == (int)YuGuDanState.WeiChuLi
                       select y).First();

            ygd.state = (int)YuGuDanState.DocumentedDirectly;
            ygd.isDone = true;

            ctx.SaveChanges();
        }

        internal YuGuDan[] GetAssignedYuGuDanByUserId(int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from ygd in ctx.YuGuDan
                    where !ygd.isDone && ygd.Assignee.id == uid && ygd.state == (int)YuGuDanState.AssignedToEmployeeToGetReport
                    select ygd).ToArray();
        }

        internal BaoGao CreateBaoGao(int uid, BaoGao baogao, Guid? ygId, Stream file)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var u = (from user in ctx.YuanGong
                     where user.id == uid
                     select user).First();

            var bg = BaoGao.CreateBaoGao(
                Guid.NewGuid(),
                baogao.wtly,
                baogao.wtr,
                baogao.wtf,
                baogao.xmzl,
                baogao.jzhmj,
                baogao.tdmj,
                baogao.yt,
                baogao.jgrq,
                baogao.jzhcg,
                baogao.pgzj,
                baogao.pgdj,
                baogao.pgjzh,
                baogao.xchkchy,
                baogao.xmqy,
                baogao.pgwchrq,
                baogao.gjshd,
                baogao.gjzyry,
                (int)BaoGaoState.Audit1,
                false,
                file != null,
                DateTime.Now);

            bg.wjbh = baogao.wjbh;

            bg.CreatorReference.EntityKey = u.EntityKey;

            if (ygId != null)
            {
                var ygd = (from y in ctx.YuGuDan
                           where y.id == ygId.Value
                           select y).First();

                bg.YuGuDanReference.EntityKey = ygd.EntityKey;
                ygd.isDone = true;
            }

            bg.xmmch = baogao.xmmch;
            bg.chqr = baogao.chqr;
            bg.gjqshrq = baogao.gjqshrq;
            bg.gjmd = baogao.gjmd;
            bg.gjff = baogao.gjff;
            bg.tddjyt = baogao.tddjyt;
            bg.fwlx = baogao.fwlx;
            bg.tdqdfsh = baogao.tdqdfsh;
            bg.rjl = baogao.rjl;
            bg.lmdj = baogao.lmdj;
            bg.tdzjg = baogao.tdzjg;

            ctx.AddToBaoGao(bg);

            ctx.SaveChanges();

            if (file != null)
            {
                var filePath = GetFilePath(bg.id);

                using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    int readCount;
                    var buffer = new byte[8192];
                    while ((readCount = file.Read(buffer, 0, buffer.Length)) != 0)
                    {
                        fs.Write(buffer, 0, readCount);
                    }
                }
            }

            return bg;
        }

        internal void Audit1BaoGaoSuccess(Guid id, string comment, int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && !b.isDone && b.state == (int)BaoGaoState.Audit1
                      select b).First();

            var u = (from user in ctx.YuanGong
                     where user.id == uid
                     select user).First();

            var bglsh = BaoGaoShenPiLiShi.CreateBaoGaoShenPiLiShi(
                bg.id,
                (int)BaoGaoState.Audit1,
                DateTime.Now,
                true,
                u.id);

            bglsh.comment = comment;


            bglsh.BaoGaoReference.EntityKey = bg.EntityKey;
            bglsh.YuanGongReference.EntityKey = u.EntityKey;

            bg.state = (int)BaoGaoState.Audit2;

            ctx.AddToBaoGaoShenPiLiShi(bglsh);

            ctx.SaveChanges();
        }

        internal void Audit2BaoGaoSuccess(Guid id, string comment, int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && !b.isDone && b.state == (int)BaoGaoState.Audit2
                      select b).First();

            var bglsh = BaoGaoShenPiLiShi.CreateBaoGaoShenPiLiShi(
                bg.id,
                (int)BaoGaoState.Audit2,
                DateTime.Now,
                true,
                uid);

            bglsh.comment = comment;

            var u = (from user in ctx.YuanGong
                     where user.id == uid
                     select user).First();

            bglsh.BaoGaoReference.EntityKey = bg.EntityKey;
            bglsh.YuanGongReference.EntityKey = u.EntityKey;

            bg.state = (int)BaoGaoState.Audit3;
            ctx.AddToBaoGaoShenPiLiShi(bglsh);

            ctx.SaveChanges();
        }

        internal void Audit3BaoGaoSuccess(Guid id, string comment, int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && !b.isDone && b.state == (int)BaoGaoState.Audit3
                      select b).First();

            var bglsh = BaoGaoShenPiLiShi.CreateBaoGaoShenPiLiShi(
                bg.id,
                (int)BaoGaoState.Audit3,
                DateTime.Now,
                true,
                uid);


            bglsh.comment = comment;

            var u = (from user in ctx.YuanGong
                     where user.id == uid
                     select user).First();

            bglsh.BaoGaoReference.EntityKey = bg.EntityKey;
            bglsh.YuanGongReference.EntityKey = u.EntityKey;

            bg.state = (int)BaoGaoState.NeedToAssignToChargeMoney;
            ctx.AddToBaoGaoShenPiLiShi(bglsh);

            ctx.SaveChanges();
        }

        internal void AuditFail(Guid id, string comment, int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && !b.isDone && (b.state == (int)BaoGaoState.Audit1 || b.state == (int)BaoGaoState.Audit2 || b.state == (int)BaoGaoState.Audit3)
                      select b).First();

            var bglsh = BaoGaoShenPiLiShi.CreateBaoGaoShenPiLiShi(
                bg.id,
                (int)bg.state,
                DateTime.Now,
                false,
                uid);

            bglsh.comment = comment;

            var u = (from user in ctx.YuanGong
                     where user.id == uid
                     select user).First();

            bglsh.BaoGaoReference.EntityKey = bg.EntityKey;
            bglsh.YuanGongReference.EntityKey = u.EntityKey;


            bg.state = (int)BaoGaoState.RequireUpdate;
            ctx.AddToBaoGaoShenPiLiShi(bglsh);

            ctx.SaveChanges();
        }

        internal BaoGao[] GetAuditFailedBaoGao(int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from b in ctx.BaoGao
                    where b.state == (int)BaoGaoState.RequireUpdate && b.Creator.id == uid && !b.isDone
                    select b).ToArray();
        }

        internal BaoGao[] GetAudit1BaoGao()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from b in ctx.BaoGao
                    where b.state == (int)BaoGaoState.Audit1 && !b.isDone
                    select b).ToArray();
        }

        internal BaoGao[] GetAudit2BaoGao()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from b in ctx.BaoGao
                    where b.state == (int)BaoGaoState.Audit2 && !b.isDone
                    select b).ToArray();
        }

        internal BaoGao[] GetAudit3BaoGao()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from b in ctx.BaoGao
                    where b.state == (int)BaoGaoState.Audit3 && !b.isDone
                    select b).ToArray();
        }

        internal BaoGaoShenPiLiShi[] GetBaoGaoAuditHistory(int uid, string wjbh)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from bgls in ctx.BaoGaoShenPiLiShi
                    where bgls.BaoGao.wjbh == wjbh && bgls.BaoGao.Creator.id == uid
                    select bgls).ToArray();
        }

        internal YuGuDan[] GetUnchargedYuGuDan(int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from ygd in ctx.YuGuDan
                    where !ygd.isDone && ygd.Assignee.id == uid && ygd.state == (int)YuGuDanState.AssignedToEmployeeToCharge && !ygd.isDone
                    select ygd).ToArray();
        }

        internal BaoGao[] GetUnchargedBaoGao(int uid)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from bg in ctx.BaoGao
                    where bg.Assignee.id == uid && !bg.isDone && bg.state == (int)BaoGaoState.AssignToEmployeeToChargeMoney
                    select bg).ToArray();
        }

        internal YuanGong[] GetAllUsers()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from e in ctx.YuanGong
                    select e).ToArray();
        }

        internal YuGuDan GetYuGuDan(Guid id)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from ygd in ctx.YuGuDan
                    where id == ygd.id
                    select ygd).First();
        }

        internal BaoGao GetBaoGao(Guid id)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from bg in ctx.BaoGao
                    where bg.id == id
                    select bg).First();
        }

        internal BaoGaoShenPiLiShi[] GetBaoGaoDetail(Guid id, out BaoGao bg)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var baogao = (from b in ctx.BaoGao
                  where b.id == id
                  select b).First();

            var h = (from bgh in ctx.BaoGaoShenPiLiShi
                     where bgh.BaoGao.id == baogao.id
                     select bgh).ToArray();

            bg = baogao;

            return h;
        }

        internal YuGuDan UpdateYuGuDan(YuGuDan yugudan)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());
            var ygd = (from y in ctx.YuGuDan
                       where y.id == yugudan.id
                      select y).First();


            ygd.fpbh = yugudan.fpbh;
            ygd.gjshd = yugudan.gjshd;
            ygd.hxqy = yugudan.hxqy;
            ygd.jgrq = yugudan.jgrq;
            ygd.jzhcg = yugudan.jzhcg;
            ygd.jzhmj = yugudan.jzhmj;
            ygd.kpje = yugudan.kpje;
            ygd.shfrq = yugudan.shfrq;
            ygd.shshje = yugudan.shshje;
            ygd.tdmj = yugudan.tdmj;
            ygd.wtf = yugudan.wtf;
            ygd.wtly = yugudan.wtly;
            ygd.xmqy = yugudan.xmqy;
            ygd.xmzl = yugudan.xmzl;
            ygd.ygdj = yugudan.ygdj;
            ygd.ygjzh = yugudan.ygjzh;
            ygd.ygzj = yugudan.ygzj;
            ygd.yt = yugudan.yt;
            ygd.bzh = yugudan.bzh;
            ygd.wtr = yugudan.wtr;

            if (string.IsNullOrEmpty(yugudan.ygbh))
            {
                ygd.ygbh = Guid.NewGuid().ToString();
            }
            else
            {
                ygd.ygbh = yugudan.ygbh;
            }

            ctx.SaveChanges();

            return ygd;

        }

        internal BaoGao UpdateBaoGao(BaoGao baogao, Stream file)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == baogao.id && b.state == (int)BaoGaoState.RequireUpdate && !b.isDone
                      select b).First();

            bg.wtly = baogao.wtly;
            bg.wtf = baogao.wtf;
            bg.xmzl = baogao.xmzl;
            bg.jzhmj = baogao.jzhmj;
            bg.tdmj = baogao.tdmj;
            bg.yt = baogao.yt;
            bg.jgrq = baogao.jgrq;
            bg.jzhcg = baogao.jzhcg;
            bg.pgzj = baogao.pgzj;
            bg.pgdj = baogao.pgdj;
            bg.pgjzh = baogao.pgjzh;
            bg.xchkchy = baogao.xchkchy;
            bg.xmqy = baogao.xmqy;
            bg.pgwchrq = baogao.pgwchrq;
            bg.gjshd = baogao.gjshd;
            bg.gjzyry = baogao.gjzyry;
            bg.hasAttachment = file != null;
            bg.wjbh = baogao.wjbh;

            bg.xmmch = baogao.xmmch;
            bg.chqr = baogao.chqr;
            bg.gjqshrq = baogao.gjqshrq;
            bg.gjmd = baogao.gjmd;
            bg.gjff = baogao.gjff;
            bg.tddjyt = baogao.tddjyt;
            bg.fwlx = baogao.fwlx;
            bg.tdqdfsh = baogao.tdqdfsh;
            bg.rjl = baogao.rjl;
            bg.lmdj = baogao.lmdj;
            bg.tdzjg = baogao.tdzjg;

            bg.state = (int)BaoGaoState.Audit1;
            ctx.SaveChanges();

            if (file != null)
            {
                var filePath = GetFilePath(bg.id);

                using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    int readCount;
                    var buffer = new byte[8192];
                    while ((readCount = file.Read(buffer, 0, buffer.Length)) != 0)
                    {
                        fs.Write(buffer, 0, readCount);
                    }
                }
            }

            return bg;
        }
        public delegate TResult Func<T1, T2, T3, T4, T5, TResult>(T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5);

        internal YuGuDan[] SearchYuGuDan(string ygbh, string wtly, string wtf, string xmzl, DateTime? gjshd, string wtr)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());


            return (from ygd in ctx.YuGuDan
                    where (string.IsNullOrEmpty(ygbh) || ygd.ygbh == ygbh) && 
                    (string.IsNullOrEmpty(wtly) || ygd.wtly.Contains(wtly)) && 
                    (string.IsNullOrEmpty(wtf) || ygd.wtf.Contains(wtf)) && 
                    (string.IsNullOrEmpty(xmzl) || ygd.xmzl.Contains(xmzl)) &&
                    (gjshd == null || (gjshd.Value.Year == ygd.gjshd.Year && gjshd.Value.Month == ygd.gjshd.Month && gjshd.Value.Day == ygd.gjshd.Day)) &&
                    (string.IsNullOrEmpty(wtr) || ygd.wtr.Contains(wtr))
                    select ygd).ToArray();
        }

        internal BaoGao[] SearchBaoGao(string wjbh, string wtly, string wtf, string xmzl, DateTime? gjshd, DateTime? pgwchrq, string gjzyry, string wtr)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());


            return (from bg in ctx.BaoGao
                    where (string.IsNullOrEmpty(wjbh) || bg.wjbh == wjbh) &&
                    (string.IsNullOrEmpty(wtly) || bg.wtly.Contains(wtly)) &&
                    (string.IsNullOrEmpty(wtf) || bg.wtf.Contains(wtf)) &&
                    (string.IsNullOrEmpty(xmzl) || bg.xmzl.Contains(xmzl)) &&
                    (gjshd == null || (gjshd.Value.Year == bg.gjshd.Year && gjshd.Value.Month == bg.gjshd.Month && gjshd.Value.Day == bg.gjshd.Day)) &&
                    (pgwchrq == null || (pgwchrq.Value.Year == bg.pgwchrq.Year && pgwchrq.Value.Month == bg.pgwchrq.Month && pgwchrq.Value.Day == bg.pgwchrq.Day)) &&
                    (string.IsNullOrEmpty(gjzyry) || bg.gjzyry == gjzyry) &&
                    (string.IsNullOrEmpty(wtr) || bg.wtr.Contains(wtr))
                    select bg).ToArray();
        }

        internal BaoGao[] GetBaoGaoNeedToCharge()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from bg in ctx.BaoGao
                    where bg.state == (int)BaoGaoState.NeedToAssignToChargeMoney && !bg.isDone
                    select bg).ToArray();
        }

        internal void AssginBaoGaoToChargeMoney(Guid id, int assigneeId, double kpje, string fpbh)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && b.state == (int)BaoGaoState.NeedToAssignToChargeMoney && !b.isDone
                       select b).First();

            var u = (from user in ctx.YuanGong
                     where user.id == assigneeId
                     select user).First();

            bg.AssigneeReference.EntityKey = u.EntityKey;
            bg.kpje = kpje;
            bg.fpbh = fpbh;
            bg.state = (int)BaoGaoState.AssignToEmployeeToChargeMoney;

            ctx.SaveChanges();

        }

        internal void ChargeYuGuDan(Guid id, double shshje)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var ygd = (from y in ctx.YuGuDan
                       where y.id == id && !y.isDone && y.state == (int)YuGuDanState.AssignedToEmployeeToCharge
                      select y).First();

            ygd.state = (int)YuGuDanState.DoneConfirm;
            ygd.shshje = shshje;

            ctx.SaveChanges();
        }

        internal void ChargeBaoGao(Guid id, double shshje)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && !b.isDone && b.state == (int)BaoGaoState.AssignToEmployeeToChargeMoney
                      select b).First();

            bg.state = (int)BaoGaoState.DoneConfirm;
            bg.shshje = shshje;

            ctx.SaveChanges();
        }

        internal BaoGao[] GetChargedBaoGao()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from b in ctx.BaoGao
                    where !b.isDone && b.state == (int)BaoGaoState.DoneConfirm
                    select b).ToArray();
        }

        internal YuGuDan[] GetChargedYuGuDan()
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            return (from y in ctx.YuGuDan
                    where !y.isDone && y.state == (int)YuGuDanState.DoneConfirm
                    select y).ToArray();
        }

        internal void ConfirmBaoGaoCharge(Guid id, DateTime shfrq)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var bg = (from b in ctx.BaoGao
                      where b.id == id && !b.isDone && b.state == (int)BaoGaoState.DoneConfirm
                      select b).First();

            bg.shfrq = shfrq;
            bg.isDone = true;

            ctx.SaveChanges();
        }

        internal void ConfirmYuGuDanCharge(Guid id, DateTime shfrq)
        {
            var ctx = CreateYaoHuaContext(MethodInfo.GetCurrentMethod());

            var ygd = (from y in ctx.YuGuDan
                       where y.id == id && !y.isDone && y.state == (int)YuGuDanState.DoneConfirm
                       select y).First();

            ygd.shfrq = shfrq;
            ygd.isDone = true;

            ctx.SaveChanges();
        }

        internal string GenerateYuGuDanDocForZhongGuoBank(Guid id)
        {
            return CreateYuGuDanDocForDownload(id, "ZhongGuo");
        }

        internal string GenerateYuGuDanDocForShenZhenBank(Guid id)
        {
            return CreateYuGuDanDocForDownload(id, "ShenZhen");
        }

        internal string GenerateYuGuDanDocForJianSheBank(Guid id)
        {
            return CreateYuGuDanDocForDownload(id, "JianShe");
        }

        private string CreateYuGuDanDocForDownload(Guid id, string fileName)
        {
            var ygd = this.GetYuGuDan(id);

            string tempFileName = string.Format(CultureInfo.InvariantCulture,
                "{0}_{1}_{2}.doc",
                ygd.ygbh,
                fileName,
                DateTime.Now.ToFileTime());

            string tempFile = string.Format(CultureInfo.InvariantCulture,
                "{0}Temp\\{1}",
                rootPath,
                tempFileName);

            string template = string.Format(CultureInfo.InvariantCulture,
                "{0}DocTemplate\\{1}.doc",
                rootPath, 
                fileName);

            File.Copy(template, tempFile, false);


            OfficeWord.Document doc = new OfficeWord.Document();

            object Obj_FileName = tempFile;
            object Visible = false;
            object ReadOnly = false;
            object missing = System.Reflection.Missing.Value;

            doc = app.Documents.Open(ref Obj_FileName, ref missing, ref ReadOnly, ref missing,
             ref missing, ref missing, ref missing, ref missing,
             ref missing, ref missing, ref missing, ref Visible,
             ref missing, ref missing, ref missing,
             ref missing);

            var t = typeof(YuGuDan);

            foreach (OfficeWord.Bookmark bm in doc.Bookmarks)
            {
                string text = "";

                if (bm.Name.StartsWith("hxqy"))
                {
                    string ct = "√";
                    string nct = "□";

                    int index = int.Parse(bm.Name[4].ToString());

                    text = index == ygd.hxqy ? ct : nct;

                }
                else
                {
                    var o = t.GetProperty(bm.Name).GetValue(ygd, null);
                    switch (bm.Name)
                    {
                        default:
                            if (o.GetType() == typeof(DateTime))
                            {
                                text = ((DateTime)o).ToShortDateString();
                            }
                            else
                            {
                                text = o.ToString();
                            }
                            break;
                    }
                }

                bm.Select();
                bm.Range.Text = text;
            }

            object IsSave = true;
            doc.Close(ref IsSave, ref missing, ref missing);

            return tempFile;
        }


        internal string GenerateBaoGaoSumary(string wjbh, string wtly, string wtf, string xmzl, DateTime? gjshd, DateTime? pgwchrq, string gjzyry, string wtr)
        {
            var bgs = this.SearchBaoGao(wjbh, wtly, wtf, xmzl, gjshd, pgwchrq, gjzyry, wtr);

            string tempFileName = string.Format(CultureInfo.InvariantCulture,
                "{0}.xls",
                Guid.NewGuid());

            string tempFile = string.Format(CultureInfo.InvariantCulture,
                "{0}Temp\\{1}",
                rootPath,
                tempFileName);

            string template = string.Format(CultureInfo.InvariantCulture,
                "{0}DocTemplate\\summary.xls",
                rootPath);

            File.Copy(template, tempFile, false);

            object Obj_FileName = tempFile;
            object Visible = false;
            object ReadOnly = false;
            object missing = System.Reflection.Missing.Value;

            OfficeExcel.Workbook ew = excelapp.Workbooks.Open(tempFile, missing,
                        missing, missing, missing,
                        missing, missing, missing,
                        missing, missing, missing,
                        missing, missing, missing,
                        missing);

            var ews = (OfficeExcel.Worksheet)ew.Worksheets[1];

            int row = 3;
            int count = 1000;

            foreach (var bg in bgs)
            {
                row++;
                count++;

                int j = 1;
                ews.Cells[row, j++] = count;
                ews.Cells[row, j++] = bg.xmmch;
                ews.Cells[row, j++] = bg.xmzl;
                ews.Cells[row, j++] = bg.chqr;
                ews.Cells[row, j++] = bg.wtf;
                ews.Cells[row, j++] = bg.gjzyry;

                if (bg.gjqshrq == null)
                {
                    ews.Cells[row, j++] = "-" + bg.gjshd.ToShortDateString();
                }
                else
                {
                    ews.Cells[row, j++] = bg.gjqshrq.Value.ToShortDateString() +  "-" + bg.gjshd.ToShortDateString();
                }
                
                if (bg.gjqshrq != null)
                {
                    ews.Cells[row, j++] = bg.gjqshrq.Value.ToShortDateString();
                }

                ews.Cells[row, j++] = bg.gjmd;
                ews.Cells[row, j++] = bg.gjff;
                ews.Cells[row, j++] = bg.tddjyt;
                ews.Cells[row, j++] = bg.fwlx;
                ews.Cells[row, j++] = bg.tdqdfsh;
                ews.Cells[row, j++] = bg.tdmj;
                ews.Cells[row, j++] = bg.jzhmj;
                ews.Cells[row, j++] = bg.rjl;
                ews.Cells[row, j++] = bg.pgdj;
                ews.Cells[row, j++] = bg.lmdj;
                ews.Cells[row, j++] = bg.pgzj;
                ews.Cells[row, j++] = bg.tdzjg;
            }

            object IsSave = true;
            ew.Close(IsSave, missing, missing);

            return tempFile;
        }
    }
}
